
# sql_metadata
from sql_metadata import Parser
import re
import os
# 清楚sql文件中评论
def clean_comment(sql):
        '''
        数据清洗
        '''
        # 删除多行注释
        sql = re.sub(r"/\*[^*]*\*+(?:[^*/][^*]*\*+)*/", "", sql)
        # 删除独立一行注释
        lines = [line for line in sql.splitlines() if not re.match("^\s*(--|#)", line)]
        # 删除代码末尾行注释
        sql1 = " ".join([re.split("--|#",line)[0] for line in lines])
        sql2 = ' '.join(sql1.split())
        return sql2

# 获取sql文件表名
def get_table_name(sqlfile):
        table_name= set()
        with open(sqlfile,mode='r',encoding='utf-8') as f:
                lines = f.readlines()
                line = clean_comment(' '.join(lines))
                line= re.sub(r'\\;','###',line)
                line=re.sub(r'overwrite','into',line,re.I)
                # 分割多条sql语句,分别进行获取表名
                for sql in line.split(';'):
                  if len(sql)>0 and sql.lower().find('truncate')<0 and sql.lower().find('delete')<0 and sql.lower().find('alter')<0 :
                        try:
                                t = Parser(sql).tables 
                                table_name.update(t)
                        except Exception:
                                print('错误的sql 语句信息：{}，文件绝对路径信息：{}'.format(sql,sqlfile))
        return table_name

if __name__ == '__main__':
      # 输出结果
      write_path='C:\\Users\\me\\Desktop\\工作日志\\20210517\\out.sql'
      with open(write_path,mode='w',encoding='utf-8') as wf:
        path="C:\\ruanjian\\svn_newhope\\ETL\programs\\数据开发\\tc-营销\\ads"
        files = os.listdir(path)
        for file in files:
                if file.lower().find('ddl')>=0:
                        continue
                filepath=path+'\\'+file
                result_set=get_table_name(filepath)
                print('{}文件的依赖的表有{}'.format(file,'\n'.join(result_set)))
                wf.write('{}文件的依赖的表有{}'.format(file,'\n'.join(result_set)))
